package lzq.aop.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.stereotype.Service;

import lzq.aop.config.DataSourceUtils;
import lzq.aop.config.JdbcTemplate;
import lzq.aop.domain.Course;

@Service
public class CourseService {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	
	public List<Course> queryAllCourse() throws SQLException {
		Connection conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery("select id,name,mark from course");
		List<Course> result = new ArrayList<>();
		while (rs!=null&& rs.next()) {
			result.add(new Course((Long) rs.getObject(1), (String) rs.getObject(2), (int) rs.getObject(3)));
		}
		rs.close();
		DataSourceUtils.close();
		return result;

	}
	
	
	public void create(Course course) throws SQLException {
		
		String sql="insert into course(name,mark) values (?,?)";
		
		//定义psc
		PreparedStatementCreator psc=con ->{
			PreparedStatement ps=con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, course.getName());
			ps.setInt(2, course.getMark());
			System.out.println("创建PreparedStatement,要执行的sql:"+sql);
			return ps;
		};
		//模仿spring-jdbc中的jdbcTemplate
		Long id=jdbcTemplate.execute(psc, ps->{
			ps.execute();
			ResultSet rs=ps.getGeneratedKeys();
			while(rs!=null && rs.next()) {
				return rs.getLong(1);
			}
			
			return null;
		});
		
		//回填id
		course.setId(id);
		
	}
	
	public int update(Course course) {
		if(course.getId()==null) {
			return 0;
		}
		String sql="update course set name=?, set mark=? where id=?";
		PreparedStatementCreator psc=(con ->{
			PreparedStatement ps=con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, course.getName());
			ps.setInt(2, course.getMark());
			ps.setLong(3, course.getId());
			return ps;
		});
		
		return jdbcTemplate.execute(psc, (ps)->{
			return ps.getUpdateCount();
		});
		
	}
	
	public int delete(Long id) {
		if(id==null) {
			return 0;
		}
		String sql="DELETE FROM course where id=?";
		
		PreparedStatementCreator psc=(con ->{
			PreparedStatement ps=con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1, id);
			return ps;
		});
		
		return jdbcTemplate.execute(psc, (ps)->{
			return ps.getUpdateCount();
		});
	}
	
}
